/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import common.Session;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import models.Note;

/**
 *
 * @author pelle
 * Edited Steffen
 * 
 * 21-06-2011, Pelle: Added getNoteCount
 */
public class NoteDb extends AbstractDb implements NoteInterface {
    
    private PersonInterface personDb = new PersonDb();
    
    public int getNoteCount() {
        int count = 0;
        
        int personId = Session.person.getId();
        
        ResultSet rs = this.db.query(""
                + "SELECT COUNT(*) AS count "
                + "FROM Lif_Note "
                + "WHERE note_public = 1 OR (note_public = 0 AND note_createdPersonid = " + personId + ")");
        
        try {
            if(rs != null && rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PersonDb.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return count;
    }
    
    public Note getNote(int id) {
        Note note = null;
        if(id > 0) {
            ResultSet rs = this.db.query(""
                    + "SELECT * "
                    + "FROM Lif_Note "
                    + "JOIN Lif_Person ON person_id = note_createdPersonid "
                    + "WHERE note_id = " + id + ";");
            
            try {
                if(rs != null && rs.next()) {
                    note = this.buildNote(rs);
                }
            } catch (SQLException ex) {
                Logger.getLogger(NoteDb.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return note;
    }
    
    public int saveNote(Note note) {
        int success = 0;
        
        if(note != null) {
            if(note.getId() > 0) {
                success = this.updateNote(note);
            } else {
                success = this.saveNewNote(note);
            }
        }
        
        return success;
    }
    
    private int updateNote(Note note) {
        int saved = 0;
        
        if(note != null) {
            int personId = Session.person.getId();
            
            // Ertatter et ' med  \' Så det ikke giver en SQL-fejl
            String title = note.getTitle().replace("'", "\\'");
            String text = note.getText().replace("'", "\\'");
            
            saved = this.db.queryUpdate(""
                    + "UPDATE Lif_Note "
                    + "SET "
                    + "note_title = '" + title + "', "
                    + "note_text = '" + text + "', "
                    + "note_updated = GETDATE(), "
                    + "note_updatedPersonid = " + personId + ", "
                    + "note_public = " + (note.isPublic() ? 1 : 0) + " "
                    + "WHERE note_id = " + note.getId() + ";");
        }
        
        return saved;
    }
    
    private int saveNewNote(Note note) {
        int saved = 0;
        
        if(note != null) {
            int personId = Session.person.getId();
            
            // Ertatter et ' med  \' Så det ikke giver en SQL-fejl
            String title = note.getTitle().replace("'", "\\'");
            String text = note.getText().replace("'", "\\'");
            
            saved = this.db.queryInsert(""
                    + "INSERT INTO Lif_Note ("
                    + "note_title, note_text, note_created, note_createdPersonid, note_updated, note_updatedPersonid, note_public"
                    + ") VALUES ("
                    + "'" + title + "', "
                    + "'" + text + "', "
                    + "GETDATE(), "
                    + personId + ", "
                    + "GETDATE(), "
                    + personId + ", "
                    + (note.isPublic() ? 1 : 0)
                    + ");");
        }
        
        return saved;
    }
    
    public ArrayList<Note> searchNotes(String text, int isPublic) {
        ArrayList<Note> notes = new ArrayList<Note>();
        
        if(text.length() > 0) {
            String query = ""
                    + "SELECT * "
                    + "FROM Lif_Note "
                    + "JOIN Lif_Person ON note_createdPersonid = person_id "
                    + "WHERE "
                    + "note_title LIKE '%" + text + "%' ";
            
            if(isPublic == 0) {
                query += "AND note_public = 0";
            } else if(isPublic == 1) {
                query += "AND note_public = 1";
            }
            
            query += ";";
            
            ResultSet rs = this.db.query(query);
            if(rs != null) {
                try {
                    while(rs.next()) {
                        notes.add(this.buildNote(rs, false));
                    }
                } catch (SQLException ex) {
                    Logger.getLogger(NoteDb.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        
        return notes;
    }
    
    private Note buildNote(ResultSet rs) {
        return buildNote(rs, true);
    }
    
    private Note buildNote(ResultSet rs, boolean addPersonDeails) {
        Note note = null;
        if(rs != null) {
            try {
                String updated = "";
                if(rs.getString("note_updated") != null && rs.getString("note_updated").length() > 0) {
                    updated = rs.getString("note_updated").substring(0, 10);
                }

                String created = "";
                if(rs.getString("note_created") != null && rs.getString("note_created").length() > 0) {
                    created = rs.getString("note_created").substring(0, 10);
                }
                
                if(addPersonDeails) {
                    note = new Note(
                        rs.getInt("note_id"),
                        rs.getString("note_title"),
                        rs.getString("note_text"),
                        rs.getInt("note_public") == 1 ? true : false,
                        created,
                        this.personDb.buildPerson(rs, false),
                        updated,
                        this.personDb.getPersonById(rs.getInt("note_updatedPersonid"), false)
                    );
                } else {
                    note = new Note(
                        rs.getInt("note_id"),
                        rs.getString("note_title"),
                        rs.getString("note_text"),
                        rs.getInt("note_public") == 1 ? true : false,
                        created,
                        updated);
                }
            } catch (SQLException ex) {
                Logger.getLogger(NoteDb.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return note;
    }
}
